﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data ;
using Microsoft.Win32;
using Maticsoft.Common;
using CustomsSystem.BLL;

namespace WEB.T_bill
{
    public partial class account : System.Web.UI.Page
    {
        DataTable dt;
        DataSet ds;
        protected void Page_Load(object sender, EventArgs e)
        {
            
            gvBind();
        }

        /// <summary>
        /// 绑定gridview
        /// </summary>
        public void gvBind()
        {
            int i = int.Parse(Session["sheetID"].ToString());
            CustomsSystem.Model.sheetInfo si = new CustomsSystem.BLL.SheetInfoManage().GetModel(i);
            
            lbCustomsName.Text = Session["CustomsName"].ToString();
            lbGuaZhang.Text = Session["CustomsName"].ToString();
            lbInvoice.Text = Session["CustomsName"].ToString();
            lbagencyDate.Text = si.agencyDate.ToShortDateString();
            lbDate.Text = DateTime.Today.ToShortDateString();
            DataTable dt1 = DS().Tables[0];
            lbNo .Text =dt1.Rows [0][2].ToString ();
            gvAccount.DataSource = DT();
            gvAccount.DataBind();
        }

        /// <summary>
        /// 处理数据库查询的数据
        /// </summary>
        /// <returns></returns>
        public DataTable DT()
        {
            ds = DS();
            DataTable dt = new DataTable("最终表");
            DataTable dt1 = ds.Tables[0];
            DataTable dt2 = ds.Tables[1];
            DataTable dt3 = ds.Tables[2];
            DataTable dt4 = ds.Tables[3];
            DataTable dt5 = ds.Tables[4];
            //给dt添加列
            dt.Columns.Add("序号", typeof(String));
            dt.Columns.Add("项目", typeof(String));
            dt.Columns.Add("    ", typeof(String));
            dt.Columns.Add("金额", typeof(String));
            dt.Columns.Add("备注", typeof(String));
            //dt.Columns.Add("发票", typeof(String));
            dt.AcceptChanges();
            //第一行数据
            DataRow dr00 = dt.NewRow();
            dt.Rows.Add(dr00);
            DataRow dr1 = dt.NewRow();
            dr1[0] = "1、已收款项";
            dr1[1] = " ";

            //if (dt1.Rows[0][3] == DBNull.Value)
            //{
            //    MessageBox.Show (this, "请先对应收账款进行销账" );
            //  // Response.Write("<Script language=JavaScript>history.go(-1);</Script>");
            //}
            //else
            //{
               
                dr1[3] = dt1.Rows[0][3];

                dt.Rows.Add(dr1);
                //空2行

                DataRow dr01 = dt.NewRow();
                DataRow dr02 = dt.NewRow();
                //DataRow dr03 = dt.NewRow();
                dt.Rows.Add(dr01); dt.Rows.Add(dr02); //dt.Rows.Add(dr03);
                DataRow dr2 = dt.NewRow();
                dr2[0] = "2、包干业务";
                dr2[1] = "应收款项金额（合计=①+②）";
                dr2[2] = "付款时间";
                //计算合计行
                //double sum = 0;
                //for (int i = 0; i < dt2.Rows.Count; i++)
                //{

                //    sum += double.Parse(dt2.Rows[i][2].ToString());

                //}
                //dr2[3] = sum;
                dt.Rows.Add(dr2);//diwuh
                //if (sum == 0)
                //{
                //    MessageBox.Show(this, "请先对应付账款进行销账");
                //   // Response.Write("<Script language=JavaScript>history.go(-1);</Script>");
                //}
                
                
                //放入详细数据
                //int rows = 6;
                //for (int i = 0; i < dt2.Rows.Count ; i++)
                //{
                //    DataRow dr = dt.NewRow();
                //    dt.Rows.Add(dr);
                //    rows = 5;
                //    rows += i;
                //    for (int j = 1; j < dt2.Columns.Count; j++)
                //    {

                //        dt.Rows[rows][j] = dt2.Rows[i][j].ToString();
                //    }
                //}
                DataRow dr03 = dt.NewRow();

                dr03[1] = "①应付帐项金额（小计现金支付）";
                double sumbgxj = 0;
                for (int i = 0; i < dt4.Rows.Count; i++)
                {

                    sumbgxj += double.Parse(dt4.Rows[i][3].ToString());

                }
                dr03[3] = sumbgxj;
                dt.Rows.Add(dr03);
                //放入详细数据(包干现金)
                int rows = 6;
                for (int i = 0; i < dt4.Rows.Count; i++)
                {
                    DataRow dr = dt.NewRow();
                    dt.Rows.Add(dr);
                    rows = 6;
                    
                    for (int j = 1; j < dt4.Columns.Count-1; j++)
                    {
                        
                        dt.Rows[rows][j] = dt4.Rows[i][j].ToString();
                        dt.Rows[rows][4] = "发票NO." + dt4.Rows[i][5].ToString() + "/" + dt4.Rows[i][4].ToString();
                    }
                    rows += 1;

                }
                DataRow dr04 = dt.NewRow();
                dt.Rows.Add(dr04);
                DataRow dr05 = dt.NewRow();


                dr05[1] = "②应付帐项金额（小计转账支付）";
                double sumbgzz = 0;
                for (int i = 0; i < dt2.Rows.Count; i++)
                {

                    sumbgzz += double.Parse(dt2.Rows[i][3].ToString());

                }
                dr05[3] = sumbgzz;

                dt.Rows.Add(dr05);
                rows = dt4.Rows.Count+2+6;
                //放入详细数据(包干转账)
                for (int i = 0; i < dt2.Rows.Count; i++)
                {
                    DataRow dr = dt.NewRow();
                    dt.Rows.Add(dr);
                    //rows = 9;
                    
                    for (int j = 1; j < dt2.Columns.Count-1; j++)
                    {


                        dt.Rows[rows][j] = dt2.Rows[i][j].ToString();
                        dt.Rows[rows][4] = "发票NO." + dt2.Rows[i][5].ToString() + "/" + dt2.Rows[i][4].ToString();
                    }
                    rows += 1;
                }
                
                DataRow dr06 = dt.NewRow();
                DataRow dr07 = dt.NewRow();


                dt.Rows.Add(dr06);
                dt.Rows.Add(dr07);
                DataRow dr3 = dt.NewRow();
                dr3[0] = "3、代收代付业务";
                dr3[1] = "应收款项金额（合计=①+②）";
                //计算合计行
                //double sum1 = 0;
                //for (int i = 0; i < dt3.Rows.Count; i++)
                //{

                //    sum1 += double.Parse(dt3.Rows[i][2].ToString());
                //}
                //dr3[3] = sum1;
               // dr3[2] = double.Parse(dr1[2].ToString()) - sum;
                dt.Rows.Add(dr3);
                //放入详细数据
                //int rows1 = 8;
                //for (int i = 0; i < dt3.Rows.Count; i++)
                //{
                //    DataRow dr = dt.NewRow();
                //    dt.Rows.Add(dr);
                //    rows1 = 8;
                //    rows1 += i;
                //    for (int j = 1; j < dt3.Columns.Count; j++)
                //    {

                //        dt.Rows[rows1][j] = dt3.Rows[i][j].ToString();
                //    }
                //}
                DataRow dr08 = dt.NewRow();
  
                 dr08[1] = "①应付帐项金额（小计现金支付）";
                double sumdsdfxj = 0;
                for (int i = 0; i < dt5.Rows.Count; i++)
                {

                    sumdsdfxj += double.Parse(dt5.Rows[i][3].ToString());
                
                }

                dr08[3] = sumdsdfxj;
                dt.Rows.Add(dr08);
                //放入详细数据(代收代付现金)
                rows = dt4.Rows.Count+dt2.Rows.Count  + 2 + 6+4;
                for (int i = 0; i < dt5.Rows.Count; i++)
                {
                    DataRow dr = dt.NewRow();
                    dt.Rows.Add(dr);
                    //rows = 16;
                    
                    for (int j = 1; j < dt5.Columns.Count-1; j++)
                    {

                        dt.Rows[rows][j] = dt5.Rows[i][j].ToString();
                        dt.Rows[rows][4] = "发票NO." + dt5.Rows[i][5].ToString() + "/" + dt5.Rows[i][4].ToString();
                    }
                    rows += 1;
                }  
                DataRow dr09 = dt.NewRow();
                DataRow dr10 = dt.NewRow();
                dt.Rows.Add(dr09);
   

                dr10[1] = "②应付帐项金额（小计转账支付）";
                double sumdsdfzz = 0;
                for (int i = 0; i < dt3.Rows.Count; i++)
                {

                    sumdsdfzz += double.Parse(dt3.Rows[i][3].ToString());

                }
                dr10[3] = sumdsdfzz;
                dt.Rows.Add(dr10);
                //放入详细数据(代收代付转账)
                rows = dt4.Rows.Count + dt2.Rows.Count+dt5.Rows.Count  + 2 + 6 + 4 + 2;
                for (int i = 0; i < dt3.Rows.Count; i++)
                {
                    DataRow dr = dt.NewRow();
                    dt.Rows.Add(dr);
                    //rows = 20;
                    
                    for (int j = 1; j < dt3.Columns.Count-1; j++)
                    {

                        dt.Rows[rows][j] = dt3.Rows[i][j].ToString();
                        dt.Rows[rows][4] = "发票NO." + dt3.Rows[i][5].ToString() + "/" + dt3.Rows[i][4].ToString();
                    }
                    rows += 1;
                }
                
                DataRow dr11 = dt.NewRow();
                DataRow dr12 = dt.NewRow();
                dt.Rows.Add(dr11);
                dt.Rows.Add(dr12);
              

                //包干和代收代付分别的应收款合计
                dr2[3] = sumbgxj + sumbgzz;
                dr3[3] = sumdsdfxj + sumdsdfzz;
               
            
                DataRow dr4 = dt.NewRow();
                dr4[0] = "4=1-2-3";
                dr4[1] = "报关收入（报关费）";
                dr4[3]=double.Parse(dr1[3].ToString()) - sumbgxj - sumbgzz-sumdsdfxj-sumdsdfzz;
                dt.Rows.Add(dr4);
                DataRow dr13 = dt.NewRow();
                DataRow dr14 = dt.NewRow();
                dt.Rows.Add(dr13);
                dt.Rows.Add(dr14);
            
                dt.AcceptChanges();

               
                
          //  }
            return dt;
        }

        /// <summary>
        /// 获取数据库数据
        /// </summary>
        /// <returns></returns>
        public DataSet DS()
        {
            CustomsSystem.BLL.BillViewManage bvm = new CustomsSystem.BLL.BillViewManage();
            int id = int.Parse(Session["sheetID"].ToString());
            string sql = " a.sheetID=" + id;
            ds = bvm.Get(sql);
            return ds;
        }

        /// <summary>
        /// 导出EXCEL
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnDrive_Click(object sender, EventArgs e)
        {
            //string name=
            int a = int.Parse(Session["sheetID"].ToString());
            string[] info = new string[] { lbCustomsName.Text, lbNo.Text, lbDate.Text,lbagencyDate.Text };
            string filepath = Server.MapPath("../document/Excel/");
            dt = DT();
            billDataToExcel datatoExcel = new billDataToExcel();
            BillManage dll = new BillManage();

            string file = datatoExcel.output (dt, "报关结算单", filepath, info);
            DownloadFile(file);
        }


        //// <summary>
        /// 下载文件
        /// </summary>
        /// <param name="filename">文件物理地址</param>
        protected void DownloadFile(string file)
        {
            string filename = "/document/Excel/" + file;
            int intStart = filename.LastIndexOf(@"\") + 1;
            string saveFileName = filename.Substring(intStart, filename.Length - intStart);

            System.IO.FileInfo fi = new System.IO.FileInfo(filename);
            string fileextname = fi.Extension;
            string DEFAULT_CONTENT_TYPE = "application/unknown";
            RegistryKey regkey, fileextkey;
            string filecontenttype;
            try
            {
                regkey = Registry.ClassesRoot;
                fileextkey = regkey.OpenSubKey(fileextname);
                filecontenttype = fileextkey.GetValue("Content Type", DEFAULT_CONTENT_TYPE).ToString();
            }
            catch
            {
                filecontenttype = DEFAULT_CONTENT_TYPE;
            }
            Response.Clear();
            Response.Charset = "gb2312";
            Response.Buffer = true;
            this.EnableViewState = false;
            Response.ContentEncoding = System.Text.Encoding.Default;
            //对文件进行编码，防止下载名出现乱码
            string encodefileName = System.Web.HttpUtility.UrlEncode(file, System.Text.Encoding.UTF8);

            Response.AppendHeader("Content-Disposition", "attachment;filename=" + encodefileName);
            Response.ContentType = filecontenttype;

            Response.WriteFile(filename);
            Response.Flush();
            Response.Close();

            Response.End();

        }

    }
}